package com.san.common.datasource;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.druid.pool.DruidPooledPreparedStatement;
import com.san.common.utils.Constant;

/**
 * JDBC连接类
 * 
 * @author Administrator
 *
 */
public class JdbcManager {
	private static final Logger logger = LoggerFactory.getLogger(JdbcManager.class);

	/**
	 * Oracle驱动
	 */
	private static final String DIVER_ORACLE = "oracle.jdbc.driver.OracleDriver";

	/**
	 * MySQL驱动
	 */
	private static final String DIVER_MYSQL = "com.mysql.jdbc.Driver";

	/**
	 * 返回连接状态
	 * 
	 * @return
	 */
	public boolean testConnection(JdbcEntity entity) {
		boolean result = false;
		Connection conn = null;
		try {

			String url = "";
			switch (entity.getSourceType()) {
			case Constant.SOURCE_TYPE_ORACLE:
				Class.forName(DIVER_ORACLE);
				url = "jdbc:oracle:thin:@" + entity.getIp() + ":" + entity.getPort() + ":" + entity.getDbName();
				break;
			case Constant.SOURCE_TYPE_MYSQL:
				Class.forName(DIVER_MYSQL);
				url = "jdbc:mysql://" + entity.getIp() + ":" + entity.getPort() + "/" + entity.getDbName()
						+ "?useUnicode=true&characterEncoding=utf8&useOldAliasMetadataBehavior=true&rewriteBatchedStatements=true";
				break;
			default:
				break;
			}
			// 获取连接
			conn = DriverManager.getConnection(url, entity.getUser(), entity.getPassword());
			if (null != conn) {
				result = true;
			}
		} catch (ClassNotFoundException e) {
			logger.error("驱动未找到", e);
		} catch (SQLException e) {
			logger.info("连接异常", e);
		} finally {
			try {
				if (null != conn && !conn.isClosed()) {
					conn.close();
				}
			} catch (SQLException e) {
				logger.info("连接关闭异常", e);
			}
		}

		return result;
	}

	/**
	 * 绑定变量方式执行查询方法
	 * 
	 * @param sql    参数使用符号“?”
	 * @param params 按sql语句中参数顺序放入
	 * @return
	 */
	public List<Map<String, Object>> executeQuery(DruidPooledConnection conn,String sql, Object[] params) {
		List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
		if (null == conn) {
			return result;
		}
		ResultSet rs = null;
		DruidPooledPreparedStatement ps = null;
		try {
			ps = (DruidPooledPreparedStatement) conn.prepareStatement(sql);
			this.setParams(ps, params);
			rs = ps.executeQuery();
			result = this.convertList(rs);
		} catch (SQLException e) {
			logger.error("SQL执行异常", e);
		} finally {
			try {
				if (null != ps) {
					ps.close();
				}
				if (null != conn) {
					conn.close();
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return result;
	}

	/**
	 * 绑定变量方式执行流式查询，用于返回大批量结果集，使用本方法需要手工关闭RS和PS
	 * 
	 * @param sql    参数使用符号“?”
	 * @param params 按sql语句中参数顺序放入
	 * @return
	 */
	public Map<String, Object> executeQueryByType(DruidPooledConnection conn,String sql, Object[] params) {
		Map<String, Object> result = new HashMap<String, Object>();
		if (null == conn) {
			return result;
		}
		ResultSet rs = null;
		DruidPooledPreparedStatement ps = null;
		try {
			ps = (DruidPooledPreparedStatement) conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,
					ResultSet.CONCUR_READ_ONLY);
			this.setParams(ps, params);
			rs = ps.executeQuery();

			result.put("rs", rs);
			result.put("ps", ps);
		} catch (SQLException e) {
			logger.error("SQL执行异常", e);
		}

		return result;
	}

	/**
	 * 绑定变量方式执行SQL方法
	 * 
	 * @param conn 连接信息
	 * @param sql    参数使用符号“?”
	 * @param params 按sql语句中参数顺序放入
	 * @return
	 */
	public boolean executeSQL(DruidPooledConnection conn,String sql, Object[] params) {
		if (null == conn) {
			return false;
		}
		// 获取连接
		boolean bool = true;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			this.setParams(ps, params);
			ps.execute();
		} catch (SQLException e) {
			bool = false;
			logger.error("SQL执行异常", e);
		} finally {
			try {
				if (null != ps) {
					ps.close();
				}
				if (null != conn) {
					conn.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return bool;
	}

	/**
	 * 批量绑定变量方式执行SQL方法
	 * 
	 * @param conn   连接信息
	 * @param sql    参数使用符号“?”
	 * @param listParams 参数集合
	 * @return
	 */
	public boolean batchExecuteSQL(DruidPooledConnection conn,String sql, List<Object[]> listParams) {
		if (null == conn) {
			return false;
		}
		// 获取连接
		boolean bool = true;
		DruidPooledPreparedStatement ps = null;
		try {
			ps = (DruidPooledPreparedStatement) conn.prepareStatement(sql);
			// 设置为手动提交
			conn.setAutoCommit(false);
			for (Object[] params : listParams) {
				this.setParams(ps, params);

				ps.addBatch();
			}

			ps.executeBatch();
			// 提交，批量插入数据库中。
			conn.commit();
		} catch (SQLException e) {
			bool = false;
			logger.error("SQL执行异常", e);
		} finally {
			try {
				if (null != ps) {
					ps.close();
				}
				if (null != conn) {
					conn.close();
				}
				
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		return bool;
	}

	/**
	 * 绑定变量
	 * 
	 * @param stmt
	 * @param params
	 * @throws SQLException
	 */
	private void setParams(PreparedStatement stmt, Object[] params) throws SQLException {
		if (null == stmt) {
			return;
		}
		if (null == params) {
			return;
		}
		int index = 1;
		for (Object obj : params) {
			stmt.setObject(index, obj);
			index++;
		}
	}

	/**
	 * 结果集转换
	 * 
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> convertList(ResultSet rs) throws SQLException {

		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();

		ResultSetMetaData md = rs.getMetaData();
		int columnCount = md.getColumnCount();
		Map<String, Object> rowData = null;

		while (rs.next()) {

			rowData = new LinkedHashMap<String, Object>();

			for (int i = 1; i <= columnCount; i++) {
				rowData.put(md.getColumnName(i).toLowerCase(), rs.getObject(i));
			}

			list.add(rowData);
		}

		return list;
	}
}
